﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_REPORT_Fit2012r_Get_Total_Fitness_Summary')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_REPORT_Fit2012r_Get_Total_Fitness_Summary
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_REPORT_Fit2012r_Get_Total_Fitness_Summary
**	Desc : 종합건강요약
**	Test Exec Query : Exec usp_UPDMS_FIT_REPORT_Fit2012r_Get_Total_Fitness_Summary '2015','ko'
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING.cs
**	Program ID : Fit2012r
**	Auth : 송시명
**	Date : 2014-06-12
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_REPORT_Fit2012r_Get_Total_Fitness_Summary]
@ls_base_year nvarchar(4),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--0. 스냅샷 (BODY_SNAPSHOT)
SELECT b.File_Dir,
       b.File_Nm,
	   b.File_Ext,
       a.Measure_Dt,
	   dbo.ufn_UPDMS_Get_Setting_Value('FIT_Opt_03') AS Show_Snapshot_Fg
  FROM UPDMS_FIT_FIGURE_MGT a WITH(NOLOCK)
  JOIN UPDMS_COM_FILE b WITH(NOLOCK) ON a.Seq = b.Activity_Id
 WHERE b.Activity_Cd = 'Fit1012m'
   AND b.File_Nm LIKE 'SS%'
 ORDER BY a.Measure_Dt DESC


--1. 키, 몸무게, BMI (WEIGHT_BMI)
SELECT TOP 1
       Measure_Dt,
       172 AS Body_Length,
       Body_Weight,
       BMI
  FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
 ORDER BY Measure_Dt DESC


--2. 근육량,체지방량,체지방율,복부지방율 (BODY_FAT_RATIO)
SELECT TOP 1
       Measure_Dt,
       Soft_Lean_Mass,
	   Body_Fat_Mass,
       Body_Fat_Ratio,
	   Waist_Hip_Fat_Ratio
  FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
 WHERE Body_Fat_Ratio > 0.00
 ORDER BY Measure_Dt DESC


--3. 총운동일수, 총일수, 운동일수비율, 총운동시간(분), 평균운동시간(분) (TOTAL_TRAINING_TIME)
SELECT X.Total_Training_Day,
       X.Total_Day,
       (CONVERT(DECIMAL(10,2), X.Total_Training_Day) / CONVERT(DECIMAL(10,2), X.Total_Day)) * CONVERT(DECIMAL(10,2), 100) AS Total_Training_Day_Avg,
       X.Total_Training_Minute,
       X.Total_Training_Minute_Avg
  FROM (
       SELECT COUNT(DISTINCT A.Training_Dt) AS Total_Training_Day,
              DATEDIFF(DAY, MIN(A.Training_Dt), MAX(A.Training_Dt)) AS Total_Day,
              SUM(DATEDIFF(MINUTE,
                           A.Training_Dt + ' ' + A.Start_Hour + ':' + A.Start_Minute,
                           A.Training_Dt + ' ' + A.End_Hour + ':' + A.End_Minute)
                 ) AS Total_Training_Minute,
              SUM(DATEDIFF(MINUTE,
                    A.Training_Dt + ' ' + A.Start_Hour + ':' + A.Start_Minute,
                    A.Training_Dt + ' ' + A.End_Hour + ':' + A.End_Minute)
          ) / COUNT(A.Training_Id) AS Total_Training_Minute_Avg
         FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
       ) X


--4. 금연, 금주 (NO_SMOKING_DRINKING)
SELECT CASE Use_Yn WHEN 'Y' THEN Setting_Value ELSE '' END Start_Dt,
       CASE Use_Yn WHEN 'Y' THEN DATEDIFF(dd, Setting_Value, GETDATE()) + 1 ELSE 0 END Pass_Day
  FROM UPDMS_CFG_SETTING_MGT WITH(NOLOCK)
 WHERE Setting_Cd IN ('FIT_Opt_01', 'FIT_Opt_02')
 ORDER BY Setting_Cd


--5.부위별 운동수행비율 (TOTAL_TRAINING_WEIGHT_COUNT)
SELECT Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(z.Training_Part, 'FIT', @ls_lang_set) AS Training_Part_Nm,
       Total_Weight,
       Total_Count
  FROM (
       SELECT C.Training_Part,
              SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
              SUM(B.Action_Count * B.Set_Count) AS Total_Count
         FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
         JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
         JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
        GROUP BY C.Training_Part
      ) z
 ORDER BY z.Training_Part


--6.요일별 운동수행비율 (TOTAL_TRAINING_WEEK_DAY)
SELECT Week_Day,
       SUM(ISNULL(z.Training_Cnt, 0)) AS Training_Cnt
  FROM (
       SELECT A.Dt as Training_Dt,
              CASE @ls_lang_set WHEN 'ko' THEN A.Day_K_Abb_Nm ELSE A.Day_E_Abb_Nm END Week_Day,
              B.Training_Cnt,
              A.View_Order
         FROM UPDMS_MST_DATE A WITH(NOLOCK)
         LEFT OUTER JOIN (
              SELECT Training_Dt,
                     COUNT(Training_Id) AS Training_Cnt
                FROM UPDMS_FIT_WEIGHT_TRAINING WITH(NOLOCK)
               GROUP BY Training_Dt
              ) B ON A.Dt = B.Training_Dt
       ) z
 GROUP BY z.Week_Day
 ORDER BY MAX(z.View_Order)


--7. 인바디(체중, 근육량, 체지방량, 복부지방율, BMI, 체지방율) (INBODY_RATIO)
SELECT X.Month_Id AS Measure_Ym,
       ISNULL(Y.Body_Weight, 0.0) AS Body_Weight,
       ISNULL(Y.Soft_Lean_Mass, 0.0) AS Soft_Lean_Mass,
       ISNULL(Y.Body_Fat_Mass, 0.00) AS Body_Fat_Mass,
       ISNULL(Y.Waist_Hip_Fat_Ratio, 0.00) AS Waist_Hip_Fat_Ratio,
	   ISNULL(Y.BMI, 0.0) AS BMI,
	   ISNULL(Y.Body_Fat_Ratio, 0.00) AS Body_Fat_Ratio
  FROM UPDMS_MST_MONTH X WITH(NOLOCK)
  LEFT OUTER JOIN (
       SELECT LEFT(Measure_Dt, 7) AS Measure_Ym,
              Body_Weight,
              BMI,
              Soft_Lean_Mass,
              Body_Fat_Mass,
              Body_Fat_Ratio,
              Waist_Hip_Fat_Ratio
         FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
        WHERE LEFT(Measure_Dt, 4) = @ls_base_year
          AND Soft_Lean_Mass > 0.00
          AND Body_Fat_Mass > 0.00
          AND Body_Fat_Ratio > 0.00
          AND Waist_Hip_Fat_Ratio > 0.00
       ) Y
    ON X.Month_Id = Y.Measure_Ym
 WHERE LEFT(X.Month_Id, 4) = @ls_base_year
 ORDER BY X.Month_Id


--8. 몸무게 (BODY_WEIGHT)
SELECT X.Month_Id AS Measure_Ym,
       AVG(ISNULL(Y.Body_Weight, 0.0)) AS Body_Weight
  FROM UPDMS_MST_MONTH X WITH(NOLOCK)
  LEFT OUTER JOIN (
       SELECT LEFT(Measure_Dt, 7) AS Measure_Ym,
              Body_Weight
         FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
        WHERE DATEPART(year, CONVERT(datetime, Measure_Dt)) = @ls_base_year
          AND Body_Weight > 0.0
       ) Y
    ON X.Month_Id = Y.Measure_Ym
 WHERE LEFT(X.Month_Id, 4) = @ls_base_year
 GROUP BY X.Month_Id
 ORDER BY X.Month_Id


--9. 최소, 최대 몸무게 (MIN_MAX_BODY_WEIGHT)
SELECT CONVERT(int, MIN(Body_Weight)) AS Min_Body_Weight,
       CONVERT(int, MAX(Body_Weight) + 1) AS Max_Body_Weight
  FROM (
       SELECT AVG(Body_Weight) AS Body_Weight
         FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
        WHERE Body_Weight > 0.0
          AND YEAR(CONVERT(datetime, Measure_Dt)) >= 2014
        GROUP BY LEFT(Measure_Dt, 7)
       ) Z 

GO